 
package com.push.notifications;


import java.util.ArrayList;
import java.util.List;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
  private static final String DATABASE_NAME="dbmobile";
  
  private static Cursor constantsCursor = null;
  
  private static final int DATABASE_VERSION = 43;//modified at 9/12/2012
  
  /*Create some tables onto database*/
  private static final String SERVICES_TABLE = "ServicesTable";
  private static final String NEWS_TABLE = "NewsTable";
  private static final String CATEGORY_TABLE = "CategoryTable";
  private static final String EXPRESS_TABLE = "ExpressTable";
  
  /***Define fields for database table***/
  private static final String Field_id = "_id";
  private static final String Field_NameCategory = "category_name";
  private static final String Field_idCategory = "category_id";
  private static final String Field_idParent = "parent_id";
  private static final String Field_command = "command";
  private static final String Field_status = "status";  // assigned or not assigned 
  
  /**Define fields of news table*/
  private static final String Field_title = "title";
  private static final String Field_desc = "short_desc";
  private static final String Field_thumnail = "thumbnail";
  private static final String Field_contentId = "extend_id";
  private static final String Field_Date = "Receive_time";
  
  /**Define fields of services table**/  
  private static final String Field_content = "content";
  private static final String Field_Link = "link";  
  
  public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }
  
  
  @Override
  public void onCreate(SQLiteDatabase db) {
	  	  
	  
	//Create category table 
	  String sQuery = "CREATE TABLE IF NOT EXISTS " + CATEGORY_TABLE+" (" + 
					 Field_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "+       	//0
					 Field_NameCategory + " TEXT, "+						  	//1
					 Field_idCategory + " INTEGER ,"+						  	//2
					 Field_idParent + " INTEGER ,"+								//3
					 Field_command + " TEXT ,"+									//4
					 Field_thumnail + " TEXT ,"+								//5
					 Field_status + " INTEGER ,"+								//6
					 Field_desc + " TEXT "+										//7
				");"; 
      try {
    	  	db.execSQL(sQuery);
      	} catch (SQLException e) {
      		Log.e("Error Create category", e.toString());
      		return;
      	}
      
      
	  //create ServicesTable
	  sQuery = "CREATE TABLE IF NOT EXISTS " + SERVICES_TABLE+" (" + 
			  		  							 Field_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "+ //0
			  		  							 Field_idCategory + " INTEGER, "+					//1
			  		  							 Field_content + " TEXT ,"+							//2	
			  		  							 Field_Date + " TEXT"+								//3
			  		  						");";  
	  try {
		  	db.execSQL(sQuery);         
	  	} catch (SQLException e) {
	  		Log.e("Error Create services", e.toString());
	  		return;
	  	}
      
	  //create news table
	  sQuery = "CREATE TABLE IF NOT EXISTS " + NEWS_TABLE+" (" + 
			  		  							 Field_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "+	//0 
			  		  							 Field_idCategory + " INTEGER, "+					//1
			  		  							 Field_title + " TEXT ,"+							//2
			  		  							 Field_desc + " TEXT ,"+							//3
			  		  							 Field_thumnail + " TEXT ,"+						//4
			  		  							 Field_contentId + " INTEGER, "+					//5
			  		  							 Field_Date + " TEXT"+								//6
			  		  						");";  
	  try {
		  	db.execSQL(sQuery);         
	  	} catch (SQLException e) {
	  		Log.e("Error Create news table", e.toString());
	  		return;
	  	}
	  
	  
	  //create express table using for lottery and gold
	  sQuery = "CREATE TABLE IF NOT EXISTS " + EXPRESS_TABLE+" (" + 
			  		  							 Field_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "+	//0 
			  		  							 Field_idCategory + " INTEGER, "+					//1
			  		  							 Field_Link + " TEXT ,"+							//2			  		  							
			  		  							 Field_content + " TEXT "+							//3			  		  							 
			  		  						");";  
	  try {
		  	db.execSQL(sQuery);         
	  	} catch (SQLException e) {
	  		Log.e("Error Create news table", e.toString());
	  		return;
	  	}
    
  }
  
  @Override
  public void onOpen(SQLiteDatabase db) {
	  		super.onOpen(db);
	  		if (!db.isReadOnly()) {
	  	   try{	
	  		db.execSQL("PRAGMA foreign_keys=ON;");
	  	 } catch (SQLException e) {
	      		Log.e("foreign_keys", e.toString());
	      	}
	  }
  	} 
  
  
  
  /**==============================================***/
  /** Insert category to category table			   ***/
  /** @Author : thien_vv 22/11/2012				   ***/
  /**==============================================***/
  public void InsertCatagory(String nameCategory ,int idCategory,int idParent,String sCommand, String icon_link,int status,String sDesc)
  {
	  SQLiteDatabase db= getWritableDatabase();	  	
	    ContentValues cv=new ContentValues();
	    cv.put(Field_NameCategory, nameCategory);
	    cv.put(Field_idCategory, idCategory);
	    cv.put(Field_idParent, idParent);
	    cv.put(Field_command, sCommand);
	    cv.put(Field_thumnail, icon_link);
	    cv.put(Field_status, status);
	    cv.put(Field_desc, sDesc);
	    try {
	    		db.insert(CATEGORY_TABLE, null, cv);
		    } catch (SQLException e) {
	      		Log.e("Insert category ", e.toString());
	      		db.close();
	      	}
	    db.close();
  }
  
  /**==============================================***/
  /** Insert new News message to News table ***/
  /** @Author : thien_vv 22/11/2012				   ***/
  /**==============================================***/
  public void InsertNews(int idCategory, String title,String sDesc,String sThumbnail,int contentId)
  {
	  	SQLiteDatabase db= getWritableDatabase();	  	
	    ContentValues cv=new ContentValues();
	    cv.put(Field_idCategory, idCategory);
	    cv.put(Field_title, title);
	    cv.put(Field_desc, sDesc);
	    cv.put(Field_thumnail, sThumbnail);
	    cv.put(Field_contentId, contentId);
	    cv.put(Field_Date, CUtilsFunction.getDate());
	    try {
    		db.insert(NEWS_TABLE, null, cv);
		} catch (SQLException e) {
      		Log.e("Insert message ", e.toString());
      		db.close();
	    }
	    db.close();
  }
  
  /**==============================================***/
  /** Insert new service message to services table ***/
  /** @Author : thien_vv 22/11/2012				   ***/
  /**==============================================***/
  public void InsertMessage(int id, String MesageNew)
  {
	  	SQLiteDatabase db= getWritableDatabase();
	  	//onOpen(db);
	    ContentValues cv=new ContentValues();
	    cv.put(Field_idCategory, id);
	    cv.put(Field_content, MesageNew);	   
	    cv.put(Field_Date, CUtilsFunction.getDate());
	    try {
    		db.insert(SERVICES_TABLE, null, cv);
		} catch (SQLException e) {
      		Log.e("Insert message ", e.toString());
      		db.close();
	    }
	    db.close();
  }
  
  
  
  /**==============================================***/
  /** get category in category table               ***/
  /** @Author : thien_vv 22/11/2012				   ***/
  /**==============================================***/
  public categoryItem getCategory(int idCategory, int idParent)
  {
	  categoryItem item = null;
	  String sQuery = "SELECT * FROM "+ CATEGORY_TABLE + " WHERE " + Field_idCategory + "=" + idCategory + " and " +Field_idParent + "="+idParent+ ";";
	  constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
	  if(constantsCursor.moveToFirst())
	  {
		  
		    String CatalogName = constantsCursor.getString(1);
		    int CategoryId = constantsCursor.getInt(2);
		    int parentId = constantsCursor.getInt(3);
		    String command = constantsCursor.getString(4);
			boolean bStatus = false;
			if(constantsCursor.getInt(6) > 0)//get state
				bStatus = true;
			String link_icon = constantsCursor.getString(5);
			//String name,String img, boolean status, int nCategory,int nIdParent,int nPos,String command, String sDate , String sDesc
			item = new categoryItem(CatalogName,link_icon,bStatus,CategoryId,parentId,0,command,"","");
	  }
	  return item;
  }
  
  /**==============================================***/
  /** update status for category in category table ***/
  /** @Author : thien_vv 22/11/2012				   ***/
  /**==============================================***/
  public int  updateStatusCategory(int idCategory,int idParent ,int nStatus)
  {
	  SQLiteDatabase db= getWritableDatabase();	  	
	    ContentValues cv=new ContentValues();	 
	    cv.put(Field_status, nStatus);	   
	   
	    int nReturn =  db.update(CATEGORY_TABLE, cv, Field_idCategory + " = ? and " + Field_idParent + " = ? ",
	    	            new String[] { String.valueOf(idCategory) ,String.valueOf(idParent)});
	    
	    db.close();
	    return nReturn;
	          
  }
  
  public void deleteDatainCategory(int nParentId)
  {
	  String sQuery = "DELETE FROM "+ CATEGORY_TABLE +" WHERE "+ Field_idParent + "= " +nParentId+" ;";
	  SQLiteDatabase db= getWritableDatabase();	
	  
	  db.execSQL(sQuery);
	  
	  db.close();
  }
  
  /**====================================================***/
  /** get message of service table ex: so xo , ti gia..	***/
  /** @Author : thien_vv 22/11/2012				   		***/
  /**===================================================***/
   public List<historyItem> getMessagebyID(int IDcataloge)
  {
	  	String sQuery = "SELECT * FROM "+ SERVICES_TABLE +" WHERE "+Field_idCategory + "= "+IDcataloge+  " ;";
	  	constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
	  	List<historyItem> listHistory =  new ArrayList<historyItem>();
	  	int id=0;	  
	  	if(constantsCursor.moveToFirst())
	  	{
	  	do {	  	
  				String sCategoryId = constantsCursor.getString(1);
				String sContent = constantsCursor.getString(2);			
				String sDate = constantsCursor.getString(3);
				listHistory.add(new historyItem(sCategoryId, sContent,sDate, id,"",-1,""));
				id++;
	  		}
	  		while (constantsCursor.moveToNext());
	  	}
	  return listHistory;
  }
   
   
   /**====================================================***/
   /** get message of service table news             	***/
   /** @Author : thien_vv 22/11/2012				   		***/
   /**===================================================***/
    public List<historyItem> getNewsByCategory(int Idcategory)
   {
 	  	String sQuery = "SELECT * FROM "+ NEWS_TABLE +" WHERE "+Field_idCategory + "= "+Idcategory+  " ORDER BY _id DESC ;";
 	  	constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
 	  	List<historyItem> listHistory =  new ArrayList<historyItem>();
 	  	int id=0;	  
 	  	if(constantsCursor.moveToFirst())
 	  	{
 	  	do {	  	
   				String sCategoryId = constantsCursor.getString(1);
   				String sTitle = constantsCursor.getString(2);
 				String sContent = constantsCursor.getString(3);	
 				String sThumbnail = constantsCursor.getString(4);	
 				String sIdContent = constantsCursor.getString(5);
 				int idContent = Integer.parseInt(sIdContent);
 				String sDate = constantsCursor.getString(6);
 				listHistory.add(new historyItem(sTitle, sContent,sDate, id,"title",idContent,sThumbnail));
 				id++;
 	  		}
 	  		while (constantsCursor.moveToNext());
 	  	}
 	  return listHistory;
   }

   
    
    /**====================================================***/
    /** get message of service table news             	***/
    /** @Author : thien_vv 22/11/2012				   		***/
    /**===================================================***/
     public String countNewsByCatgoryId(int Idcategory)
    {
  	  	String sQuery = "SELECT COUNT(*) FROM "+ NEWS_TABLE +" WHERE "+Field_idCategory + "= "+Idcategory+  " ;";//INC
  	  	constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
  	  	List<historyItem> listHistory =  new ArrayList<historyItem>();
  	  	String sCount = "0";	  
  	  	if(constantsCursor.moveToFirst())
  	  	{ 	
  	  	sCount = constantsCursor.getString(0);

  	  	}
  	  return sCount;
    }

     
   public List<categoryItem> getAllCategoryByParentId(int nParentId)
  {
	  	 
	  String sQuery = "SELECT * FROM "+ CATEGORY_TABLE + " WHERE " + Field_idParent + " = "+nParentId +" ;";
	  constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
	  List<categoryItem> listCategory = new ArrayList<categoryItem>();
	  int index=0;
	  if(constantsCursor.moveToFirst())
	  	{
	  	do {	  	
		  		String CatalogName = constantsCursor.getString(1);
			    int CategoryId = constantsCursor.getInt(2);
			    int parentId = constantsCursor.getInt(3);
			    String command = constantsCursor.getString(4);			
				String link_icon = constantsCursor.getString(5);
				boolean bStatus = false;
				if(constantsCursor.getInt(6) > 0)//get state
					bStatus = true;
				String sDesc = constantsCursor.getString(7);
				
				//String name,String img, boolean status, int nCategory,int nIdParent,int nPos,String command, String sDate , String sDesc
				listCategory.add(new categoryItem(CatalogName,link_icon,bStatus,CategoryId,parentId,index,command,"",sDesc)); //for_test
				index++;
	  		}
	  		while (constantsCursor.moveToNext());
	  	}
	  
	  return listCategory;
  }
  
   public int  getLastestNews()
  {
	  int CategoryId = -1;	 
	  String sQuery = "SELECT "+Field_idCategory+" FROM "+NEWS_TABLE+" WHERE _id=( SELECT MAX(_id) FROM "+NEWS_TABLE+") ;";
	  constantsCursor = getReadableDatabase().rawQuery(sQuery,null);	  
	  
	  if(constantsCursor != null && constantsCursor.moveToFirst())
	  	{
			 CategoryId = constantsCursor.getInt(0);
	  	}
	  
	  return CategoryId;
  }
  
   public List<categoryItem> getListSubNewsHistory(int nParentId)
  {
	  	 
	  String sQuery = "SELECT * FROM "+ CATEGORY_TABLE + " WHERE " + Field_idParent + " = "+nParentId +" and "+ Field_status + " = 1" +" ;";
	  constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
	  List<categoryItem> listCategory = new ArrayList<categoryItem>();
	  int index=0;
	  if(constantsCursor != null && constantsCursor.moveToFirst())
	  	{
	  	do {	  	
		  		String CatalogName = constantsCursor.getString(1);
			    int CategoryId = constantsCursor.getInt(2);
			    int parentId = constantsCursor.getInt(3);
			    String command = constantsCursor.getString(4);			
				String link_icon = constantsCursor.getString(5);
				boolean bStatus = false;
				if(constantsCursor.getInt(6) > 0)//get state
					bStatus = true;
				String sDesc = constantsCursor.getString(7);
				
				//String name,String img, boolean status, int nCategory,int nIdParent,int nPos,String command, String sDate , String sDesc
				listCategory.add(new categoryItem(CatalogName,link_icon,bStatus,CategoryId,parentId,index,command,"",sDesc)); //for_test
				index++;
	  		}
	  		while (constantsCursor.moveToNext());
	  	}
	  
	  return listCategory;
  }
   /**====================================================***/
   /** update express table by id				          ***/
   /** @Author : thien_vv 22/11/2012				   	  ***/
   /**===================================================***/
  
   public int updateExpressionById(int nCategoryId,String link,String content)
   {
	   SQLiteDatabase db= getWritableDatabase();
	   if(isExistCategoryInExpress(nCategoryId))
	   {
	    ContentValues cv=new ContentValues();	 
	    cv.put(Field_Link, link);
	    cv.put(Field_content, content);
	   
	    int nReturn =  db.update(EXPRESS_TABLE, cv, Field_idCategory + " = ? ",
	    	            new String[] { String.valueOf(nCategoryId) });
	    
	   
	    return nReturn;
	   }
	   else //insert new
	   {
		  
		    ContentValues cv=new ContentValues();
		    cv.put(Field_idCategory, nCategoryId);
		    cv.put(Field_Link, link);
		    cv.put(Field_content, content);	   		   
		    try {
	    		db.insert(EXPRESS_TABLE, null, cv);
			} catch (SQLException e) {
	      		Log.e("Insert message ", e.toString());	      		
		    }		   
	   }
	   db.close();
	   return 0;
   }
   
   
   private boolean isExistCategoryInExpress(int categoryId)
   {
	   String sQuery = "SELECT * FROM "+ EXPRESS_TABLE + " WHERE " + Field_idCategory + "="+categoryId;
	   
	   constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
	   if(constantsCursor != null && constantsCursor.getCount() > 0)
		   return true;	  
	   return false;
   }
   
   
   public String getLinkInExpressById(int categoryId)
   {
	   String sLink ="";
	   String sQuery = "SELECT "+Field_Link+" FROM "+ EXPRESS_TABLE + " WHERE " + Field_idCategory + "="+categoryId;
	   constantsCursor = getReadableDatabase().rawQuery(sQuery,null);
	   if(constantsCursor != null && constantsCursor.getCount() > 0 && constantsCursor.moveToFirst())
	  	{
		   sLink = constantsCursor.getString(0);
	  	}
	   
	   return sLink;
   }
   /**====================================================***/
   /** update all tables in database when change version  ***/
   /** @Author : thien_vv 22/11/2012				   	  ***/
   /**===================================================***/
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    android.util.Log.w(SERVICES_TABLE, "Upgrading database, which will destroy all old data");
    try {
    	db.execSQL("DROP TABLE IF EXISTS "+ SERVICES_TABLE);
    } catch (SQLException e) {
  		Log.e("Drop message ", e.toString());  	
  	}
    
    try {
    	db.execSQL("DROP TABLE IF EXISTS "+ NEWS_TABLE);
    } catch (SQLException e) {
  		Log.e("Drop message ", e.toString());  	
  	}
    
    
    try {
    	db.execSQL("DROP TABLE IF EXISTS "+ CATEGORY_TABLE);
    } catch (SQLException e) {
  		Log.e("Drop message ", e.toString());  	
  	}
    
    try {
    	db.execSQL("DROP TABLE IF EXISTS "+ EXPRESS_TABLE);
    } catch (SQLException e) {
  		Log.e("Drop message ", e.toString());  	
  	}
    onCreate(db);
  }
}
